﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using DBUtility;

namespace SQLServerDAL
{
    public class SystemUserBase
    {
        public SystemUserBase()
        { }

        /// <summary>
        /// 添加系统用户
        /// </summary>
        public string Add(Model.SystemUserBase _user)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@SystemUserId", SqlDbType.UniqueIdentifier, 16, "OutPut"),
					new SqlParameter("@BusinessUnitId", SqlDbType.UniqueIdentifier, 16),
					new SqlParameter("@Username", SqlDbType.VarChar, 256),
					new SqlParameter("@Password", SqlDbType.VarChar, 256),
					new SqlParameter("@PersonalEmail", SqlDbType.VarChar, 256),
					new SqlParameter("@InternalEmail", SqlDbType.VarChar, 256),
                    new SqlParameter("@InternalEmailPassword", SqlDbType.VarChar, 256),
					new SqlParameter("@FullName", SqlDbType.VarChar, 128),
					new SqlParameter("@NickName", SqlDbType.VarChar, 128),
					new SqlParameter("@Salutation", SqlDbType.VarChar, 128),
					new SqlParameter("@MobilePhone", SqlDbType.VarChar, 256),
					new SqlParameter("@BusinessPhone", SqlDbType.VarChar, 256),
					new SqlParameter("@Homephone", SqlDbType.VarChar, 256),
					new SqlParameter("@Fax", SqlDbType.VarChar, 128),
					new SqlParameter("@WindowsLiveID", SqlDbType.VarChar, 256),
					new SqlParameter("@Comment", SqlDbType.NVarChar),
					new SqlParameter("@CreatedBy", SqlDbType.UniqueIdentifier, 16),
					new SqlParameter("@CreatedOn", SqlDbType.DateTime),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
            };
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = new Guid(_user.BusinessUnitId);
            parameters[2].Value = _user.Username;
            parameters[3].Value = _user.Password;
            parameters[4].Value = _user.PersonalEmail;
            parameters[5].Value = _user.InternalEmail;
            parameters[6].Value = _user.InternalEmailPassword;
            parameters[7].Value = _user.FullName;
            parameters[8].Value = _user.NickName;
            parameters[9].Value = _user.Salutation;
            parameters[10].Value = _user.MobilePhone;
            parameters[11].Value = _user.BusinessPhone;
            parameters[12].Value = _user.Homephone;
            parameters[13].Value = _user.Fax;
            parameters[14].Value = _user.WindowsLiveID;
            parameters[15].Value = _user.Comment;
            parameters[16].Value = new Guid(_user.CreatedBy);
            parameters[17].Value = _user.CreatedOn;
            parameters[18].Direction = ParameterDirection.ReturnValue;

            int status = 0;
            string result = "";
            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "SystemUser_CreateUser", parameters);
                status = ((parameters[18].Value != null) ? ((int)parameters[18].Value) : -1);
                if (status == 0)
                {
                    result = "succeeded|" + parameters[0].Value;
                }
                else if (status == 1)
                {
                    result = "用户名已存在!"; ///exists;
                }
                else if (status == -1)
                {
                    result = "存储过程执行错误!"; ///storedprocedure error
                }
            }
            catch
            {
                result = "程序执行错误!"; ///procedure error
            }

            return result;
        }

        /// <summary>
        /// 匹配ID更新系统用户信息
        /// </summary>
        public string Update(Model.SystemUserBase _user)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@SystemUserId", SqlDbType.UniqueIdentifier, 16),
					new SqlParameter("@BusinessUnitId", SqlDbType.UniqueIdentifier, 16),
					new SqlParameter("@Username", SqlDbType.VarChar, 256),
					new SqlParameter("@Password", SqlDbType.VarChar, 256),
					new SqlParameter("@PersonalEmail", SqlDbType.VarChar, 256),
					new SqlParameter("@InternalEmail", SqlDbType.VarChar, 256),
					new SqlParameter("@FullName", SqlDbType.VarChar, 128),
					new SqlParameter("@NickName", SqlDbType.VarChar, 128),
					new SqlParameter("@Salutation", SqlDbType.VarChar, 128),
					new SqlParameter("@MobilePhone", SqlDbType.VarChar, 256),
					new SqlParameter("@BusinessPhone", SqlDbType.VarChar, 256),
					new SqlParameter("@Homephone", SqlDbType.VarChar, 256),
					new SqlParameter("@Fax", SqlDbType.VarChar, 128),
					new SqlParameter("@WindowsLiveID", SqlDbType.VarChar, 256),
					new SqlParameter("@Comment", SqlDbType.NVarChar),
                    new SqlParameter("@IsApproved", SqlDbType.Bit),
                    new SqlParameter("@IsLockedOut", SqlDbType.Bit),
					new SqlParameter("@ModifiedOn", SqlDbType.DateTime),
					new SqlParameter("@ModifiedBy", SqlDbType.UniqueIdentifier, 16),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
            };
            parameters[0].Value = new Guid(_user.SystemUserId);
            parameters[1].Value = new Guid(_user.BusinessUnitId);
            parameters[2].Value = _user.Username;
            parameters[3].Value = _user.Password;
            parameters[4].Value = _user.PersonalEmail;
            parameters[5].Value = _user.InternalEmail;
            parameters[6].Value = _user.FullName;
            parameters[7].Value = _user.NickName;
            parameters[8].Value = _user.Salutation;
            parameters[9].Value = _user.MobilePhone;
            parameters[10].Value = _user.BusinessPhone;
            parameters[11].Value = _user.Homephone;
            parameters[12].Value = _user.Fax;
            parameters[13].Value = _user.WindowsLiveID;
            parameters[14].Value = _user.Comment;
            parameters[15].Value = _user.IsApproved ? 1 : 0;
            parameters[16].Value = _user.IsLockedOut ? 1 : 0;
            parameters[17].Value = _user.ModifiedOn;
            parameters[18].Value = new Guid(_user.ModifiedBy);
            parameters[19].Direction = ParameterDirection.ReturnValue;

            int status = 0;
            string result = "";
            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "SystemUser_UpdateUser", parameters);
                status = ((parameters[19].Value != null) ? ((int)parameters[19].Value) : -1);
                if (status == 0)
                {
                    result = "succeeded|" + _user.SystemUserId;
                }
                else if (status == 1)
                {
                    result = "用户名已存在!"; ///exists;
                }
                else if (status == -1)
                {
                    result = "存储过程执行错误!"; ///storedprocedure error
                }
            }
            catch
            {
                result = "程序执行错误!"; ///procedure error
            }
            return result;
        }

        /// <summary>
        /// 匹配ID获取系统用户实体信息
        /// </summary>
        public Model.SystemUserBase GetObjById(string systemUserId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM vw_System_UserBase WHERE [SystemUserId] = @SystemUserId");

            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@SystemUserId", SqlDbType.UniqueIdentifier) };
            parameters[0].Value = new Guid(systemUserId);

            Model.SystemUserBase _user = null;
            try
            {
                SqlDataReader rd = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
                if (rd.Read())
                {
                    _user = new Model.SystemUserBase();
                    _user.SystemUserId = systemUserId;
                    _user.BusinessUnitId = rd["BusinessUnitId"].ToString().Trim();
                    _user.BusinessUnitName = rd["BusinessUnitName"].ToString();
                    _user.Username = rd["Username"].ToString().Trim();
                    _user.PersonalEmail = rd["PersonalEmail"].ToString().Trim();
                    _user.InternalEmail = rd["InternalEmail"].ToString().Trim();
                    _user.InternalEmailPassword = rd["InternalEmailPassword"].ToString().Trim();
                    _user.FullName = rd["FullName"].ToString().Trim();
                    _user.NickName = rd["NickName"].ToString().Trim();
                    _user.Salutation = rd["Salutation"].ToString().Trim();
                    _user.MobilePhone = rd["MobilePhone"].ToString().Trim();
                    _user.BusinessPhone = rd["BusinessPhone"].ToString().Trim();
                    _user.Homephone = rd["Homephone"].ToString().Trim();
                    _user.Fax = rd["Fax"].ToString().Trim();
                    _user.WindowsLiveID = rd["WindowsLiveID"].ToString().Trim();
                    _user.Fax = rd["Fax"].ToString().Trim();
                    _user.IsApproved = (rd["IsApproved"].ToString().ToLower() == "true") ? true : false;
                    _user.IsLockedOut = (rd["IsLockedOut"].ToString().ToLower() == "true") ? true : false;
                    _user.Comment = rd["Comment"].ToString().Trim();
                }
                rd.Close();
                rd.Dispose();
            }
            catch
            {
                return null;
            }
            return _user;
        }

        /// <summary>
        /// 匹配用户名获取系统用户实体信息
        /// </summary>
        public Model.SystemUserBase GetObjByUsername(string username)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM vw_System_UserBase WHERE [Username] = @Username");

            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@Username", SqlDbType.VarChar, 256)
            };
            parameters[0].Value = username;

            Model.SystemUserBase _user = null;
            try
            {
                SqlDataReader rd = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
                if (rd.Read())
                {
                    _user = new Model.SystemUserBase();
                    _user.SystemUserId = rd["SystemUserId"].ToString().Trim(); ;
                    _user.BusinessUnitId = rd["BusinessUnitId"].ToString().Trim();
                    _user.BusinessUnitName = rd["BusinessUnitName"].ToString();
                    _user.Username = rd["Username"].ToString().Trim();
                    _user.PersonalEmail = rd["PersonalEmail"].ToString().Trim();
                    _user.InternalEmail = rd["InternalEmail"].ToString().Trim();
                    _user.InternalEmailPassword = rd["InternalEmailPassword"].ToString().Trim();
                    _user.FullName = rd["FullName"].ToString().Trim();
                    _user.NickName = rd["NickName"].ToString().Trim();
                    _user.Salutation = rd["Salutation"].ToString().Trim();
                    _user.MobilePhone = rd["MobilePhone"].ToString().Trim();
                    _user.BusinessPhone = rd["BusinessPhone"].ToString().Trim();
                    _user.Homephone = rd["Homephone"].ToString().Trim();
                    _user.Fax = rd["Fax"].ToString().Trim();
                    _user.WindowsLiveID = rd["WindowsLiveID"].ToString().Trim();
                    _user.Fax = rd["Fax"].ToString().Trim();
                    _user.IsApproved = (rd["IsApproved"].ToString().ToLower() == "true") ? true : false;
                    _user.IsLockedOut = (rd["IsLockedOut"].ToString().ToLower() == "true") ? true : false;
                    _user.Comment = rd["Comment"].ToString().Trim();
                }
                rd.Close();
                rd.Dispose();
            }
            catch
            {
                return null;
            }
            return _user;
        }

        /// <summary>
        /// 匹配 ID 删除系统用户
        /// isLogical = True 逻辑删除, isLogical = false 物理删除
        /// </summary>
        public string Delete(string systemUserId, bool isLogical)
        {
            StringBuilder strSql = new StringBuilder();
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@SystemUserId", SqlDbType.UniqueIdentifier) };
            parameters[0].Value = new Guid(systemUserId);

            string result = "";
            try
            {
                if (isLogical)
                {
                    strSql.Append("UPDATE [System_UserBase] SET DeletionStateCode = 1 WHERE [SystemUserId] = @SystemUserId");
                    SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
                }
                else
                {
                    strSql = new StringBuilder();
                    strSql.Append("DELETE FROM System_UsersInRoles WHERE [SystemUserId] = @SystemUserId;");
                    strSql.Append("DELETE FROM System_UserBase WHERE [SystemUserId] = @SystemUserId");
                    SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
                }
                result = "succeeded";
            }
            catch(Exception err)
            {
                result = err.Message; ///procedure error
            }
            return result;
        }

        /// <summary>
        /// 匹配 ID 设置帐号 锁定/解锁
        /// isLockout = True 锁定, isLockout = false 解锁
        /// </summary>
        public string UnLockout(string systemUserId, bool isLockout)
        {
            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@SystemUserId", SqlDbType.UniqueIdentifier),
                new SqlParameter("@IsLockedOut", SqlDbType.Bit),
                new SqlParameter("@ReturnValue", SqlDbType.Int)
            };
            parameters[0].Value = new Guid(systemUserId);
            parameters[1].Value = isLockout;
            parameters[2].Direction = ParameterDirection.ReturnValue;

            string result = "";
            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "SystemUser_UnlockUser", parameters);
                int status = ((parameters[2].Value != null) ? ((int)parameters[2].Value) : -1);
                if (status == 0)
                {
                    result = "设置成功!";
                }
                else if (status == -1)
                {
                    result = "存储过程执行错误!"; ///storedprocedure error
                }
            }
            catch
            {
                result = "程序执行错误!"; ///procedure error
            }

            return result;
        }

        /// <summary>
        /// 验证用户名是否唯一
        /// false 可用, true 已存在
        /// </summary>
        /// <param name="username"></param>
        /// <param name="userid"></param>
        /// <param name="organizationId"></param>
        /// <returns></returns>
        public bool IsExisted(string username, string userid)
        {
            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@SystemUserId", SqlDbType.UniqueIdentifier, 16),
                new SqlParameter("@Username", SqlDbType.VarChar, 200)
            };

            parameters[0].Value = new Guid(userid);
            parameters[1].Value = username;

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT COUNT(1) FROM [System_UserBase] WHERE Username = @Username AND SystemUserId <> @SystemUserId AND DeletionStateCode = 0");

            object _obj = SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);

            bool _exists = false;
            if (_obj != null)
            {
                if ((int)_obj > 0)
                    _exists = true;
            }

            return _exists;
        }

        /// <summary>
        /// 管理员登录验证
        /// </summary>
        /// <param name="strManagerName">用户名</param>
        /// <param name="strPassword">密码</param>
        /// <returns>验证结果</returns>
        public string ValidateUserLogin(string usersname, string password)
        {
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@UsersName", SqlDbType.VarChar, 256),
                new SqlParameter("@Password", SqlDbType.VarChar, 256),
                new SqlParameter("@Result", SqlDbType.VarChar, 100)
            };
            parms[0].Value = usersname;
            parms[1].Value = password;
            parms[2].Direction = ParameterDirection.Output;

            string result;
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "ValidateUserLogin", parms);
                result = parms[2].Value.ToString().Trim();
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        /// <summary>
        /// 修改 Email 及其密码
        /// </summary>
        public string ChangePassword(string username, string password)
        {
            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@Username", SqlDbType.VarChar, 256),
                new SqlParameter("@Password", SqlDbType.VarChar, 256)
            };
            parameters[0].Value = username;
            parameters[1].Value = password;

            string result = "";
            try
            {
                StringBuilder SQL = new StringBuilder();
                SQL.Append("UPDATE System_UserBase SET [Password] = @Password WHERE Username = @Username AND DeletionStateCode = 0;");
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL.ToString(), parameters);

                result = "succeeded"; ///procedure error
            }
            catch(Exception err)
            {
                result = err.Message; ///procedure error
            }

            return result;
        }

    }
}
